package com.homework.dao;

import com.homework.common.StringUtils;
import com.homework.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.Instant;

@Repository
public class UserDao {
    @Autowired
    private Connection conn;

    /**
     * 插入用户数据，自动获取当前时间和自增ID
     * @param user
     * @return
     */
    public int insert(User user) {
        String sql = "INSERT INTO user (username, password, salt, phone, sex, age, img, status, create_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            // 自动生成盐值
            String salt = StringUtils.getSalt(user.getUsername());
            String saltedPassword = StringUtils.md5Encoder(user.getPassword(), salt);

            ps.setString(1, user.getUsername());
            ps.setString(2, saltedPassword); // 加盐后的密码
            ps.setString(3, salt); // 盐值
            ps.setString(4, user.getPhone()); // 电话号码
            ps.setInt(5, user.getSex()); // 性别
            ps.setInt(6, user.getAge()); // 年龄
            ps.setString(7, user.getImg()); // 头像图片路径
            ps.setInt(8, user.getStatus()); // 状态
            ps.setTimestamp(9, Timestamp.from(Instant.now())); // 当前时间

            int affectedRows = ps.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        user.setUserId(generatedKeys.getInt(1)); // 获取自增的user_id
                    }
                }
            }
            return affectedRows; // 返回受影响的行数
        } catch (SQLException e) {
            e.printStackTrace(); // 输出异常信息
        }
        return 0; // 插入失败返回0
    }

    /**
     * 用户登录验证
     * @param username
     * @param password
     * @return
     */
    public User login(String username, String password) {
        String sql = "SELECT * FROM user WHERE username = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String salt = rs.getString("salt");
                String saltedPassword = StringUtils.md5Encoder(password, salt);

                if (saltedPassword.equals(rs.getString("password"))) {
                    User user = new User()
                            .setUserId(rs.getInt("user_id"))
                            .setUsername(rs.getString("username"))
                            .setPassword(rs.getString("password"))
                            .setSalt(salt)
                            .setPhone(rs.getString("phone"))
                            .setSex(rs.getInt("sex"))
                            .setAge(rs.getInt("age"))
                            .setImg(rs.getString("img"))
                            .setStatus(rs.getInt("status"))
                            .setCreateTime(rs.getTimestamp("create_time"))
                            .setUpdate_time(rs.getTimestamp("update_time"))
                            .setLast_login_time(rs.getTimestamp("last_login_time"))
                            .setNickname(rs.getString("nickname"))
                            .setEmail(rs.getString("email"));

                    return user; // 返回找到的用户对象
                }
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 输出异常信息
        }
        return null; // 没找到用户返回null
    }

    /**
     * 统计用户总数
     * @return
     */
    public int countAllUsers() {
        String sql = "SELECT COUNT(*) AS total FROM user";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt("total"); // 返回用户总数
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 输出异常信息
        }
        return 0; // 查询失败返回0
    }
}
